﻿using System;
using System.Data;
using System.Text;
using System.Data.SqlClient;
using DataLib.DBUtility;//Please add references
namespace DataLib.DAL
{
	/// <summary>
	/// 数据访问类:Tally
	/// </summary>
	public partial class Tally
	{
		#region  ExtensionMethod
        /// <summary>
        /// 提交事件，所有的理货数据状态更新为1，为正式数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool BupdateStatus(int? id)
        {
            string strsql = "";
          //更新处理前的状态
            strsql = "Update TallyAfter set status=1 where Tallyid=" + id.ToString();
            int i = DbHelperSQL.ExecuteSql(strsql);
            bool flag1 = true;
            bool flag2 = true;
            bool flag = true;
            if (i < 1)
            {
                flag1 = false;
            }
            strsql = "Update TallyBefore set status=1 where tallyid=" + id.ToString();
            i = DbHelperSQL.ExecuteSql(strsql);
            if (i < 1)
            {
                flag2 = false;
            }
            flag = flag1 || flag2;
            strsql = "Update Tally set status=1 where id=" + id.ToString();
            i = DbHelperSQL.ExecuteSql(strsql);
            if (i < 1 )
            {
                flag = false;
            }



            return flag;
        }

        /// <summary>
        /// 提交事件，所有的理货数据状态更新为1，为正式数据
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public bool BupdateStatus(Model.Tally model)
        {
            string strsql = "";
            //更新处理前的状态
            strsql = "Update TallyAfter set status=1 where Tallyid=" + model.id.ToString();
            int i = DbHelperSQL.ExecuteSql(strsql);
            bool flag1 = true;
            bool flag2 = true;
            bool flag = true;
            if (i < 1)
            {
                flag1 = false;
            }
            strsql = "Update TallyBefore set status=1 where tallyid=" + model.id.ToString();
            i = DbHelperSQL.ExecuteSql(strsql);
            if (i < 1)
            {
                flag2 = false;
            }
            flag = flag1 || flag2;
            
            model.status = 1;
            model.id = Add(model);
            if( flag == true && Update(model)==true)
            {
                flag = true;
            }

            

            return flag;
        }


        /// <summary>
        /// 获取原料理货数据
        /// </summary>
        /// <returns></returns>
        public DataTable GetTallyItem(string loadnum,
                            string cum,
                            string dtime1,
                            string dtime2,
                            string b_goodscode,
                            string b_boxid,
                            string b_ldh,
                            string classtype,
                            string a_goodscode,
                            string a_boxid,
                            string a_ldh
                            )
        {

            StringBuilder strSql = new StringBuilder();
            //创建查询结果临时表
            strSql.Append("CREATE TABLE #tenp_tallyItem(");
	        strSql.Append("tallyid int NULL,");
	        strSql.Append("cjono varchar(15) NULL,");
	        strSql.Append("loadnum varchar(15) NULL,");
	        strSql.Append("InWrlNo varchar (50) NULL,");
	        strSql.Append("OperDate datetime NULL,");
	        strSql.Append("collectdate datetime NULL,");
	        strSql.Append("cumcode varchar(50) NULL,");
	        strSql.Append("cumname varchar(50) NULL,");
	        strSql.Append("[afterid] [int] NULL,");
	        strSql.Append("a_goodscode [nvarchar](30) NULL,");
	        strSql.Append("a_goodsname [nvarchar](30) NULL,");
	        strSql.Append("a_nwt [decimal](10, 2) NULL,");
	        strSql.Append("a_qty [int] NULL,");
	        strSql.Append("a_boxcount int NULL,");
	        strSql.Append("a_rcode [nvarchar](30) NULL,");
	        strSql.Append("a_rname [nvarchar](50) NULL,");
	        strSql.Append("a_ldh [nvarchar](30) NULL,");
	        strSql.Append("a_class [nvarchar](30) NULL,");
	        strSql.Append("a_packageno [nvarchar](30) NULL,");
	        strSql.Append("a_remark [nvarchar](100) NULL,");
	        strSql.Append("[befid] [int] NULL,");
	        strSql.Append("b_goodscode [nvarchar](30) NULL,");
	        strSql.Append("b_goodsname [nvarchar](30) NULL,");
	        strSql.Append("b_nwt [decimal](12, 2) NULL,");
	        strSql.Append("b_qty [int] NULL,");
	        strSql.Append("b_rcode [varchar](30) NULL,");
	        strSql.Append("b_rname [varchar](30) NULL,");
	        strSql.Append("b_ldh [nvarchar](30) NULL,");
	        strSql.Append("b_class [nvarchar](30) NULL,");//			--处理前联单号
	        strSql.Append("b_packageno [nvarchar](30) NULL,");
	        strSql.Append("b_remark [nvarchar](200) NULL);");
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@LoadNo",SqlDbType.VarChar,15),
                new SqlParameter("@Cum",SqlDbType.VarChar,50),
                new SqlParameter("@StartDate",SqlDbType.DateTime),
                new SqlParameter("@EndDate",SqlDbType.DateTime),
                new SqlParameter("@b_Code",SqlDbType.VarChar,20),
                new SqlParameter("@b_ldh",SqlDbType.VarChar,20),
                new SqlParameter("@b_boxid",SqlDbType.VarChar,20),
                new SqlParameter("@classfy",SqlDbType.VarChar,25),
               new SqlParameter("@a_Code",SqlDbType.VarChar,20),
                new SqlParameter("@a_ldh",SqlDbType.VarChar,20),
                new SqlParameter("@a_boxid",SqlDbType.VarChar,20)
             };
            sqlparameter[0].Value = loadnum;
            sqlparameter[1].Value = cum;
            if (dtime1.Trim() == "" || dtime2.Trim() == "")
            {
            }
            else
            {
                sqlparameter[2].Value = dtime1;
                sqlparameter[3].Value = dtime2;
            }
            sqlparameter[4].Value = b_goodscode;
            sqlparameter[5].Value = b_ldh;
            sqlparameter[6].Value = b_boxid;
            sqlparameter[7].Value = classtype;
            sqlparameter[8].Value = a_goodscode;
            sqlparameter[9].Value = a_ldh;
            sqlparameter[10].Value = a_boxid;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_TrallyItems", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }



        public DataTable GetTallyProc(string LoadNo,string CustomName,string CjoNo,
            string Wrlno, string classify, string conduct, string beginDate, string endDate, string belong, string graphic)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@LoadNo",SqlDbType.VarChar,15),
                new SqlParameter("@CustomName",SqlDbType.VarChar,30),
                new SqlParameter("@CjoNo",SqlDbType.VarChar,18),
                new SqlParameter("@Wrlno",SqlDbType.VarChar,15),
                new SqlParameter("@classify",SqlDbType.VarChar,15),
                new SqlParameter("@conduct",SqlDbType.VarChar,15),
                new SqlParameter("@beginDate",SqlDbType.DateTime),
                new SqlParameter("@endDate",SqlDbType.DateTime),
                new SqlParameter("@belong",SqlDbType.VarChar,15),
                new SqlParameter("@graphic",SqlDbType.VarChar,15)
             };
            sqlparameter[0].Value = LoadNo;
            sqlparameter[1].Value = CustomName;
            sqlparameter[2].Value = CjoNo;
            sqlparameter[3].Value = Wrlno;
            sqlparameter[4].Value = classify;
            sqlparameter[5].Value = conduct;
            if (beginDate != "" && endDate != "")
            {
                sqlparameter[6].Value = beginDate;
                sqlparameter[7].Value = endDate;  
            }
            sqlparameter[8].Value = belong;
            sqlparameter[9].Value = graphic;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_Tally", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;

        }

        /// <summary>
        /// 取得可进行原料理货的列表 Load列表
        /// </summary>
        /// <param name="city"></param>
        /// <returns></returns>
        public DataTable GetTallyList(string city,string cumid)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@city",SqlDbType.VarChar,20),
                new SqlParameter("@cumid",SqlDbType.Int)
             };
            sqlparameter[0].Value = city;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_TallyGoods", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }


        /// <summary>
        /// 根据Load单查一单原料理货信息
        /// </summary>
        /// <param name="guilei">归类</param>
        /// <param name="loadid">Load单的ID</param>
        /// <returns></returns>
        public DataTable GetTallyDetail(string guilei, int loadid)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@guilei",SqlDbType.VarChar,20),
                new SqlParameter("@loadid",SqlDbType.Int)
             };
            sqlparameter[0].Value = guilei;
            sqlparameter[1].Value = loadid;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_TallyDetails", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }

        /// <summary>
        /// 根据Load单查一单原料理货信息
        /// </summary>
        /// <param name="guilei">归类</param>
        /// <param name="loadid">理货ID</param>
        /// <returns></returns>
        public DataTable GetTallyDetailByid(string guilei, int tallyid)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@guilei",SqlDbType.VarChar,20),
                new SqlParameter("@tallyid",SqlDbType.Int)
             };
            sqlparameter[0].Value = guilei;
            sqlparameter[1].Value = tallyid;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_TallyDetails_ID", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }

        /// <summary>
        /// 根据Loadid取到理货ID，如果没有理货返回0
        /// </summary>
        /// <param name="loadid"></param>
        /// <returns></returns>
        public int GetTallyID(int loadid)
        {
            int tallyid = 0;
            DataTable dt = GetList("loadid=" + loadid.ToString()).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                tallyid = int.Parse(dt.Rows[0]["id"].ToString().Trim());
            }
            return tallyid;
        }

        /// <summary>
        /// 根据LoadID取得分类入库单的ID，如果没有则返回0
        /// </summary>
        /// <param name="loadid"></param>
        /// <returns></returns>
        public Model.InStorage GetInsID(int loadid)
        {
            int insid = 0;
            DataTable dt = DbHelperSQL.Query(@"select ins.id,ins.wrlno,l.loadnum from InStorage ins,Cjo c,LoadNum l
where ins.cjoid = c.id and c.loadnumid=l.id and LEFT(wrlno,2)='RF' and l.id=" + loadid).Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                insid = int.Parse(dt.Rows[0]["id"].ToString().Trim());
                Model.InStorage insmodel = new InStorage().GetModel(insid);
                return insmodel;
            }
            return null;
            

        }


        /// <summary>
        /// 根据Ｌｏａｄ取得所有原料处理前数据
        /// </summary>
        /// <param name="loadno"></param>
        /// <returns></returns>
        public DataTable GetBarList_F(string loadno)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@loadno",SqlDbType.VarChar,20)
             };
            sqlparameter[0].Value = loadno;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GetBarList_F", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }


        /// <summary>
        /// 根据Ｌｏａｄ取得所有原料处理后数据
        /// </summary>
        /// <param name="loadno"></param>
        /// <returns></returns>
        public DataTable GetUnList_F(string loadno)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@loadno",SqlDbType.VarChar,20)
             };
            sqlparameter[0].Value = loadno;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GetunProcessed_F", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }



        /// <summary>
        /// 某一Load下的理货列表
        /// </summary>
        /// <param name="loadno"></param>
        /// <returns></returns>
        public DataTable GetTallyList(int loadid,int outid)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@loadid",SqlDbType.Int)
                ,new SqlParameter("@outid",SqlDbType.Int)
             };
            sqlparameter[0].Value = loadid;
            sqlparameter[1].Value = outid;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_TallyList", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }

        /// <summary>
        /// 取最新原料理货编号
        /// <param name="delnoid">如果是从删除中取号就传回删除表的ID</param>
        /// </summary>
        /// <returns></returns>
        public string GetMaxTallyNo(out int delnoid)
        {
            string strno = "";
            DataTable dt = new DataTable();
            tab_delNo delno = new tab_delNo();
            dt = delno.GetList("tabname='tally' and isdel=0").Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                strno = dt.Rows[0]["delno"].ToString();
                delnoid = int.Parse(dt.Rows[0]["id"].ToString());
            }
            else
            {
                dt = null;
                StringBuilder strSql = new StringBuilder();
                strSql.Append(" select 'L' + (select Right('0000000'+LTRIM(ISNULL(MAX(Cast(Right(tallyno,7) as int)) ,0)+1),7) from Tally)");
                dt = DbHelperSQL.Query(strSql.ToString()).Tables[0];
                if (dt != null && dt.Rows.Count > 0)
                {
                    strno = dt.Rows[0][0].ToString();
                }
                delnoid = 0;
            }
            return strno;
        }

        /// <summary>
        /// 获取Load的第一单称重收集日期
        /// </summary>
        /// <param name="loadid"></param>
        /// <returns></returns>
        public string getCollDate(int loadid)
        {
            string collDate = "";
            DataTable dt = new InStorage().GetList("loadnumid=" + loadid.ToString() + " and left(wrlno,1)='M' order by instoragetime").Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                collDate = dt.Rows[0]["instoragetime"].ToString();
            }
            return collDate;
            
        }

        /// <summary>
        /// 获取Load的分类最后入库日期
        /// </summary>
        /// <param name="loadid"></param>
        /// <returns></returns>
        public string getInsDate(int loadid)
        {
            string insDate = "";
            string loadnum = "";
            
            if (loadid != 0) {
                loadnum = new LoadNum().GetModel(loadid).loadnum;
            }
            DataTable dt = new OutStorage().GetList("VLoadNum='" + loadnum + "' and left(outStorageNo,1)='F' order by outStorageDate desc ").Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                insDate = dt.Rows[0]["outStorageDate"].ToString();
                insDate = DateTime.Parse(insDate).ToString("yyyy年MM月dd日");
            }
            return insDate;

        }

        /// <summary>
        /// 根据Tally获取Load的收集日期
        /// </summary>
        /// <param name="c">C代表第一单称重 F代表最后一单分类</param>
        /// <param name="id">loadid或者outid</param>
        /// <param name="type">load是原load理货 outs是移库理库</param>
        /// <returns></returns>
        public string getWrlDate(int id, string type, string c)
        {
            string insDate = "";
            OutStorage o = new OutStorage();
            LoadNum l = new LoadNum();
            DataTable dt = new DataTable();
            if (type == "load")
            {
                Model.LoadNum m = l.GetModel(id);
                dt = new InStorage().GetList("vloadnum='" + m.loadnum + "' and (left(wrlno,1)='M' or left(wrlno,2)='RZ') order by instoragetime").Tables[0];

                if (dt != null && dt.Rows.Count > 0)
                {
                    string wrlno = dt.Rows[0]["wrlno"].ToString();
                    if (wrlno.Substring(0, 2) == "RZ")
                    {
                        if (c == "F")
                        {
                            insDate = dt.Rows[0]["instoragetime"].ToString();
                        }
                        else
                        {
                            dt = new Cjo().GetList("id=" + dt.Rows[0]["cjoid"].ToString() + " order by cjocolldate ").Tables[0];
                            insDate = dt.Rows[0]["cjocolldate"].ToString();
                        }

                    }
                    else if (c=="M")
                    {
                        insDate = dt.Rows[0]["instoragetime"].ToString();
                    }
                    else
                    {
                        dt = new OutStorage().GetList("VLoadNum='" + m.loadnum + "' and left(outStorageNo,1)='F' order by outStorageDate desc ").Tables[0];
                        if (dt != null && dt.Rows.Count > 0)
                            insDate = dt.Rows[0]["outStorageDate"].ToString();
                    }
                    
                }
            }
            else if (type == "outs")
            {
                Model.OutStorage m = o.GetModel(id);
                if (c == "M")
                {
                    dt = new InStorage().GetList("outid='" + id + "' and left(wrlno,1)='" + c + "' order by instoragetime").Tables[0];
                    if (dt != null && dt.Rows.Count > 0)
                        insDate = dt.Rows[0]["instoragetime"].ToString();
                }
                else
                {
                    dt = new OutStorage().GetList("VLoadNum='" + m.VLoadNum + "' and left(outStorageNo,1)='F' order by outStorageDate desc ").Tables[0]; ;
                    if (dt != null && dt.Rows.Count > 0)
                        insDate = dt.Rows[0]["outStorageDate"].ToString();
                }
            }
            if (insDate !="")
            {
                
                insDate = DateTime.Parse(insDate).ToString("yyyy年MM月dd日");
            }
            return insDate;

        }


        /// <summary>
        /// 判断分类入库是否完成
        /// </summary>
        /// <param name="loadid"></param>
        public string GetClassifyStatus(string loadnum)
        {
            string tallystatus = "";
            string outstatus = "";
            DataTable dt = new OutStorage().GetList("Left(outStorageNo,1)='F' and VLoadNum='" + loadnum +"'").Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                outstatus = dt.Rows[0]["status"].ToString();
                if (outstatus == "0")
                    tallystatus = "分类已完成";
                else
                    tallystatus = "分类未完成";
            }
            else
            {
                tallystatus = "未分类";
            }
            return tallystatus;
         }

        /// <summary>
        /// 获取移库称重收集日期
        /// </summary>
        /// <param name="loadid"></param>
        /// <returns></returns>
        public string getOutCollDate(int outid)
        {
            string collDate = "";
            DataTable dt = new InStorage().GetList("outid=" + outid.ToString() + " and left(wrlno,1)='M'").Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                collDate = dt.Rows[0]["instoragetime"].ToString();
            }
            return collDate;

        }

        /// <summary>
        /// 获取移库称重分类日期
        /// </summary>
        /// <param name="loadid"></param>
        /// <returns></returns>
        public string getOutsInsDate(string vload)
        {
            string collDate = "";
            DataTable dt = new OutStorage().GetList("VLoadNum='" + vload + "' and left(outStorageNo,1)='F'").Tables[0];
            if (dt != null && dt.Rows.Count > 0)
            {
                collDate = dt.Rows[0]["outStorageDate"].ToString();
            }
            return collDate;

        }


        /// <summary>
        /// 根据移库ID取得所有原料处理前数据
        /// </summary>
        /// <param name="loadno"></param>
        /// <returns></returns>
        public DataTable GetUnList_Outs_F(int outid)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@outid",SqlDbType.Int)
             };
            sqlparameter[0].Value = outid;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GetOutsunProcessed_F", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }

        /// <summary>
        /// 根据移库ID取得所有原料处理后数据
        /// </summary>
        /// <param name="loadno"></param>
        /// <returns></returns>
        public DataTable GetBarList_Outs_F(int outid)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@outid",SqlDbType.Int)
             };
            sqlparameter[0].Value = outid;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GetBarList_OutsF", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }


        /// <summary>
        /// 根据移库ID取得所有原料处理后数据
        /// </summary>
        /// <param name="loadno"></param>
        /// <returns></returns>
        private DataTable GetOutsTab(int outid)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@outid",SqlDbType.Int)
             };
            sqlparameter[0].Value = outid;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GetOutsInfo_Tally", sqlparameter);
            dt.Load(dr);
            dr.Close();
            return dt;
        }

        /// <summary>
        /// 根据移库ID及字段名，取得对应值
        /// </summary>
        /// <param name="outid"></param>
        /// <param name="filename"></param>
        /// <returns></returns>
        public string GetOutsInfo(int outid, string filename)
        {
            string str = "";
            DataTable dt = GetOutsTab(outid);
            if (dt != null && dt.Rows.Count > 0)
            {
                if (dt.Rows[0][filename] != null)
                    str = dt.Rows[0][filename].ToString();
            }
            return str;
        }


        /// <summary>
        /// 获取原料理货剩余重量
        /// </summary>
        /// <returns></returns>
        public decimal GetTallyWeight(int dataid,int nwttype)
        {
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@dataid",SqlDbType.Int),
                new SqlParameter("@nwttype",SqlDbType.Int)
             };
            sqlparameter[0].Value = dataid;
            sqlparameter[1].Value = nwttype;

            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_GetTallyWeight", sqlparameter);
            dt.Load(dr);
            dr.Close();
            if (dt != null && dt.Rows.Count > 0)
                return decimal.Parse(dt.Rows[0][0].ToString());
            else
                return 0;
        }

        /// <summary>
        /// 取得可进行原料理货的列表 Load列表
        /// </summary>
        /// <param name="city"></param>
        /// <returns></returns>
        public DataTable GetTallyListByStore(string city, string cumid,int currpage,int length,out int totalRecord,string search)
        {
            totalRecord = 0;
            DataTable dt = new DataTable();
            SqlParameter[] sqlparameter ={
                new SqlParameter("@city",SqlDbType.VarChar,20),
                new SqlParameter("@cumid",SqlDbType.Int),
                new SqlParameter("@currpage",SqlDbType.Int),
                new SqlParameter("@Length",SqlDbType.Int),
                new SqlParameter("@totalcount",SqlDbType.Int),
                new SqlParameter("@search",SqlDbType.VarChar)


             };
            sqlparameter[0].Value = city;
            sqlparameter[1].Value = cumid;
            sqlparameter[2].Value = currpage;
            sqlparameter[3].Value = length;
            sqlparameter[4].Value = totalRecord;
            sqlparameter[4].Direction = ParameterDirection.Output;
            sqlparameter[5].Value = search;
            SqlDataReader dr = DbHelperSQL.RunProcedure("EWMS_TallyGoodsByStore", sqlparameter);
            dt.Load(dr);
            totalRecord = Convert.ToInt32(sqlparameter[4].Value);
            dr.Close();
            return dt;
        }



		#endregion  ExtensionMethod

            	
	}



}

